<?php
/**
 * Created by PhpStorm.
 * User: yuanjie
 * Date: 2016/10/31
 * Time: 下午3:07
 */

$api_service_path = __DIR__ . "/../../../api/";
ob_start();
require_once($api_service_path . "public/index.php");
ob_end_clean();

//$date = '20161110';
$date = date('Ymd',time()-86400);
$begin_date = date("Y-m-d H:i:s",strtotime($date) - Config::get("config.report_time_diff"));
$end_date = date("Y-m-d H:i:s",strtotime($date) - Config::get("config.report_time_diff") + 24*60*60);
$report = array();
$mail = array(
    'yuanjie@anhuibj.com',
    'husheng@anhuibj.com',
    'ahljt@anhuibj.com',
    'ahnjk@anhuibj.com',
    'kf@anhuibj.com',
    'ahlwk@anhuibj.com',
    'ahdss@anhuibj.com',
);

//开启的门店
$shops = DB::table('maintenance_shops')
    ->select(array('id','name'))
    ->where('status',1)
    ->get();
foreach ($shops as $_shop){
    if($_shop->id == 9) continue;//赤峰暂不统计
    $standard_orders = DB::select("SELECT 
      a.order_id
      FROM orders as a, order_product as b, users as c
      where a.order_id = b.order_id
      and a.user_id = c.id
      and a.status in (1018,1017,1217,1317,1417)
      and date(a.pay_time) = ?
      and b.product_type = 'base' 
      and b.product_id = 2
      and b.status = 1
      and c.type = 1
      and a.shop_id = ? ", array($date,$_shop->id));

    $non_standard_orders = DB::select("SELECT 
      a.order_id
      FROM orders as a, order_product as b, users as c
      where a.order_id = b.order_id
      and a.user_id = c.id
      and a.status in (1018,1017,1217,1317,1417)
      and date(a.pay_time) = ?
      and b.product_type = 'non_standard' 
      and b.product_id = 247
      and b.status = 1
      and c.type = 1
      and a.shop_id = ? ", array($date,$_shop->id));

    $standard_reports = DB::select("
      select distinct order_id from order_detection where order_id in (
        SELECT 
        a.order_id
        FROM orders as a, order_product as b, users as c
        where a.order_id = b.order_id
        and a.user_id = c.id
        and a.status in (1018,1017,1217,1317,1417)
        and date(a.pay_time) = ?
        and b.product_type = 'base' 
        and b.product_id = 2
        and b.status = 1
        and c.type = 1
        and a.shop_id = ?
      ) ", array($date,$_shop->id));

    $non_standard_reports = DB::select("
      select distinct order_id from order_detection where order_id in (
        SELECT 
        a.order_id
        FROM orders as a, order_product as b, users as c
        where a.order_id = b.order_id
        and a.user_id = c.id
        and a.status in (1018,1017,1217,1317,1417)
        and date(a.pay_time) = ?
        and b.product_type = 'non_standard' 
        and b.product_id = 247
        and b.status = 1
        and c.type = 1
        and a.shop_id = ?
      ) ", array($date,$_shop->id));

    $order_reports_arr = array();
    foreach ($standard_reports as $_s_o_r){
        array_push($order_reports_arr,$_s_o_r->order_id);
    }
    foreach ($non_standard_reports as $_n_o_r){
        array_push($order_reports_arr,$_n_o_r->order_id);
    }
    $order_non_reports_arr = array();
    foreach ($standard_orders as $_s_o){
        if(!in_array($_s_o->order_id,$order_reports_arr)){
            array_push($order_non_reports_arr,$_s_o->order_id);
        }
    }
    foreach ($non_standard_orders as $_n_o){
        if(!in_array($_n_o->order_id,$order_reports_arr)){
            array_push($order_non_reports_arr,$_n_o->order_id);
        }
    }


    $standard_users = DB::select("SELECT 
      distinct a.user_id
      FROM orders as a, order_product as b, users as c
      where a.order_id = b.order_id
      and a.user_id = c.id
      and a.status in (1018,1017,1217,1317,1417)
      and date(a.pay_time) = ?
      and b.product_type = 'base' 
      and b.product_id = 2
      and b.status = 1
      and c.type = 1
      and a.shop_id = ? ", array($date,$_shop->id));

    $non_standard_users = DB::select("SELECT 
      distinct a.user_id
      FROM orders as a, order_product as b, users as c
      where a.order_id = b.order_id
      and a.user_id = c.id
      and a.status in (1018,1017,1217,1317,1417)
      and date(a.pay_time) = ?
      and b.product_type = 'non_standard' 
      and b.product_id = 247
      and b.status = 1
      and c.type = 1
      and a.shop_id = ? ", array($date,$_shop->id));

    $shop_create_users = DB::select("select 
      distinct a.phone 
      from users as a ,orders as b 
      where a.id = b.user_id 
      and a.register_source_id = 14 
      and a.status = 1 
      and a.created_at between ? and ? 
      and date(b.created_at) = ? 
      and b.shop_id = ? ",array($begin_date,$end_date,$date,$_shop->id));

    $phone = array();
    foreach ($shop_create_users as $_phone){
        array_push($phone,$_phone->phone);
    }


    $weixin_users = DB::table('sms_send_records')
        ->distinct(array('phone'))
        ->where('status',1)
        ->whereIn('phone',$phone)
        ->whereBetween('created_at',array($begin_date,$end_date))
        ->get();

    $_report = array(
        'shop_id' => $_shop->id,
        'shop_name' => $_shop->name,
        'car_num' => count($standard_orders) + count($non_standard_orders),
        'report_num' => count($standard_reports) + count($non_standard_reports),
        'user_num' => count($standard_users) + count($non_standard_users),
        'shop_user_num' => count($shop_create_users),
        'weixin_user_num' => count($weixin_users),
        'non_report_orders' => json_encode($order_non_reports_arr)
    );
    array_push($report,$_report);
}

$report_data = array(
    'mail' => $mail,
    'name' => $date.'日 门店30项检测执行情况报告',
    'date' => $date,
    'data' => $report
);

Mail::send('check_30_mail',$report_data, function($message) use ($report_data){
    $message->to($report_data['mail'], $report_data['name'])->subject($report_data['name']);
});

exit;
echo $date." report\n";
echo "门店ID,门店名称,应检车辆,实检车辆,总用户数,新用户数,转化用户数,无报告订单\n";
foreach ($report as $_r){
    echo $_r['shop_id'].",";
    echo $_r['shop_name'].",";
    echo $_r['car_num'].",";
    echo $_r['report_num'].",";
    echo $_r['user_num'].",";
    echo $_r['shop_user_num'].",";
    echo $_r['weixin_user_num'].",";
    echo $_r['non_report_orders']."\n";
}
